////////////////////////////////////////////////////////////////////////////////
// Copyright 2006 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   USER
// AUTHOR           :   Victor Zheng 
// DATE CREATE      :   2006/02/01
// DESCRIPTION      :   a DAO file for the function, USER
//                  :   
//                  :   
//                  :   
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2006/02/01   Victor Zheng    1.0                 source is generated.
// 2006/06/20   William Xu      1.0                 BugZilla #6277
//
//
////////////////////////////////////////////////////////////////////////////////

package com.erp.app.admin.userSetup.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.erp.app.admin.userSetup.criteria.UserSrchCriteria;
import com.erp.app.admin.userSetup.pk.UserPk;
import com.erp.app.admin.userSetup.vo.UserVo;
import com.erp.app.common.ProjectConstants;
import com.erp.app.common.util.ProjectCommonUtil;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.dao.SimpleSearchMaintStyleDao;
import com.erp.framework.model.pk.SimpleBasePk;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBigInteger;

/**
* <p>A <strong>DAO</strong>(Data Access Object) abstracts and encapsulates
* all access to the persistent store.  The DAO manages the connection with the
* data source to obtain and store data.  Also, the DAO implements
* the access mechanism required to work with the data source,
* and always provides a uniform API to its clients.
*
* @author          Victor Zheng 
* @version 1.0     2006/02/01
* @since           FrameWork 1.0
*
*/
public class UserDao extends SimpleSearchMaintStyleDao {

	/**
	* Returns the SQL select statement.
	* @return        a string containing sql query
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlSelectByPrimaryKey(){

		return sqlFind();
	}

	/**
	* Sets the values of a result set to a pk
	* @param         pStmt   the incoming prepared statement
	* @param         pk      the searching primary key
	* @return        a vo containing the result set values
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForSelectByPrimaryKey(PreparedStatement pStmt, SimpleBasePk pk) throws SQLException{

		setValuesForFind(pStmt, pk);
	}

	/**
	* Returns the constructed model from a result set.
	* @param         rs     the incoming result set
	* @return        a constructed VO from the result set
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected SimpleBaseVo getModelForSelectByPrimarKey(ResultSet rs) throws SQLException {

		return getResultSetValues(rs);
	}


	/**
	* Returns the SQL select statement.
	* @param         criteria     the searching criteria
	* @return        a string containing sql query
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlSelectByCriteria(SimpleBaseSrchCriteria criteria) {

		return sqlQuery(criteria);
	}

	/**
	* Returns the constructed model from a result set.
	* @param         rs     the incoming result set
	* @return        a constructed VO from the result set
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected SimpleBaseVo getModelForSelectByCriteria(ResultSet rs) throws SQLException {

		return getResultSetValues(rs);
	}

	/**
	* Returns the constructed primary key from a result set.
	* @param         rs     the incoming result set
	* @return        a constructed PK from the result set
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected SimpleBasePk getPkForSelectByCriteria(ResultSet rs) throws SQLException {

//		ErrorMsgLabelPk pk = new ErrorMsgLabelPk(rs.getString("COMPANY_CODE"),
//			rs.getString("GROUP_ID"), rs.getString("MENU_ID"), rs.getString("OBJECT_ID"),
//			rs.getString("LANG_CODE"));
//		return pk;

		return null;
	}


	/**
	* Returns the SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing sql query
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlQuery(SimpleBaseSrchCriteria obj) {

		UserSrchCriteria criteria = (UserSrchCriteria) obj;
		String sql = "";
		String whereClause = toWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "ORDER BY USER_CODE, COMPANY_CODE";

		if(!criteria.isSearchResult()){
			sql = "SELECT USER_CODE, COMPANY_CODE, USER_NAME, EMAIL, PASSWORD, "
			+ "ROLE_GROUP_ID, GROUP_LEVEL, ACTIVATION, CREATE_USER, CREATE_DATE, "
			+ "UPDATE_USER, UPDATE_DATE, USER_GROUP_CODE, DEPT, CONTENT_TYPE, "
			+ "SIGNATURE, SALESMAN_FLAG, QA_HANDLER, QC_HANDLER, APPROVER, "
			+ "GMM, DIVISION, COMPANY_TYPE, NT_TEAM, CUST_CODE  "
			+ "FROM T_USER "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		} else {
			sql = "SELECT USER_CODE, COMPANY_CODE, USER_NAME, EMAIL, PASSWORD, "
				+ "ROLE_GROUP_ID, GROUP_LEVEL, ACTIVATION, CREATE_USER, CREATE_DATE, "
				+ "UPDATE_USER, UPDATE_DATE, USER_GROUP_CODE, DEPT, CONTENT_TYPE, "
				+ "SIGNATURE, SALESMAN_FLAG, QA_HANDLER, QC_HANDLER, APPROVER, "
				+ "GMM, DIVISION, COMPANY_TYPE, NT_TEAM, CUST_CODE  "
				+ "FROM T_USER "
				+ whereClause
				+ groupByClause
				+ orderByClause;
		}
		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Returns the where clause of a SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing the where clause
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String toWhereClause(UserSrchCriteria crit) {

		UserSrchCriteria criteria = (UserSrchCriteria) crit;
		StringBuffer whereClause = new StringBuffer();

		if (criteria.getSrchCompanyCode() != null) {
			whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
			whereClause.append(" COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase()+"' ");
		}
		
		ProjectCommonUtil.addHdrBetween(whereClause, "USER_CODE", criteria.getSrchUserCodeFr(), criteria.getSrchUserCodeTo());
		ProjectCommonUtil.addHdrBetween(whereClause, "USER_NAME", criteria.getSrchUserNameFr(), criteria.getSrchUserNameTo());
		ProjectCommonUtil.addHdrBetween(whereClause, "ROLE_GROUP_ID", criteria.getSrchRoleGroupIdFr(), criteria.getSrchRoleGroupIdTo());

		return whereClause.toString();
	}

	/**
	* Returns an array list of child VOs by the parent
	* @param         parent   the incoming parent VO
	* @param         conn     the database connection
	* @return        an array list of child VOs
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	public ChildList selectByParent(SimpleBaseVo parent, Connection conn)
		throws FindRecordException {

		return null;
	}

	/**
	* Constructs a string of SQL insert statement
	* @return        a string of SQL insert statement
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlInsert() {

		String sql = "INSERT INTO T_USER ("
			+ "USER_CODE, COMPANY_CODE, USER_NAME, EMAIL, PASSWORD, "
			+ "ROLE_GROUP_ID, GROUP_LEVEL, ACTIVATION, CREATE_USER, CREATE_DATE, "
			+ "UPDATE_USER, UPDATE_DATE, USER_GROUP_CODE, DEPT, CONTENT_TYPE, "
			+ "SIGNATURE, SALESMAN_FLAG, QA_HANDLER, QC_HANDLER, APPROVER, "
			+ "GMM, DIVISION, COMPANY_TYPE, NT_TEAM, CUST_CODE ) "
			+ "VALUES ("
			+ "?, ?, ?, ?, ?, "
			+ "?, ?, ?, ?, SYSDATE, "
			+ "?, ?, ?, ?, ?, "
			+ "?, ?, ?, ?, ?, "
			+ "?, ?, ?, ?, ? )";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL insert or update statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForInsertAndModify(PreparedStatement pStmt, Object obj, String opType)
		throws SQLException {

		UserVo vo = (UserVo) obj;
		int i = 1;

		pStmt.setString(i++, vo.getUserCode());
		pStmt.setString(i++, vo.getCompanyCode());
		setNullableString(pStmt, i++, vo.getUserName());
		setNullableString(pStmt, i++, vo.getEmail());
		setNullableString(pStmt, i++, vo.getPassword());
		setNullableString(pStmt, i++, vo.getRoleGroupId());
		setNullableBigInteger(pStmt, i++, vo.getGroupLevel());
		setNullableString(pStmt, i++, vo.getActivation());
		setNullableString(pStmt, i++, vo.getCreateUser());

		if (ProjectConstants.DB_OP_UPDATE.equals(opType))
			setNullableTimestamp(pStmt, i++, vo.getCreateDate());

		setNullableString(pStmt, i++, vo.getUpdateUser());

		if (ProjectConstants.DB_OP_INSERT.equals(opType))
			setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
		setNullableString(pStmt, i++, vo.getUserGroupCode());
		setNullableString(pStmt, i++, vo.getDept());
		setNullableString(pStmt, i++, vo.getContentType());
		setNullableBytes(pStmt, i++, vo.getSignature());
		setNullableString(pStmt, i++, vo.getSalesmanFlag());
		setNullableString(pStmt, i++, vo.getQaHandlerFlag());
		setNullableString(pStmt, i++, vo.getQcHandlerFlag());
		setNullableString(pStmt, i++, vo.getApproverFlag());
		setNullableString(pStmt, i++, vo.getGmmFlag());    
		setNullableString(pStmt, i++, vo.getUserDivision());
        setNullableString(pStmt, i++, vo.getHdrCompanyType());   
        setNullableString(pStmt, i++, vo.getHdrNtTeam());    
        setNullableString(pStmt, i++, vo.getHdrCustCode());    
        
		
		if(ProjectConstants.DB_OP_UPDATE.equals(opType)){
			pStmt.setString(i++, vo.getUserCode());
			pStmt.setString(i++, vo.getCompanyCode());
			setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
		}
	}

	/**
	* Sets the values for a SQL insert statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForInsert(PreparedStatement pStmt, Object obj)
		throws SQLException {

		setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_INSERT);
	}

	/**
	* Constructs a string of SQL update statement
	* @return        a string of SQL update statement
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlUpdate() {

		String sql = "UPDATE T_USER SET USER_CODE=?, COMPANY_CODE=?, USER_NAME=?, EMAIL=?, PASSWORD=?, "
			+ "ROLE_GROUP_ID=?, GROUP_LEVEL=?, ACTIVATION=?, CREATE_USER=?, CREATE_DATE=?, "
			+ "UPDATE_USER=?, UPDATE_DATE=SYSDATE, USER_GROUP_CODE=?, DEPT=?, CONTENT_TYPE=?, "
			+ "SIGNATURE=?, SALESMAN_FLAG=?, QA_HANDLER=?, QC_HANDLER=?, APPROVER=?, "
			+ "GMM=?, DIVISION=?,COMPANY_TYPE=?, NT_TEAM=?, CUST_CODE=? "
			+ "WHERE USER_CODE=? AND COMPANY_CODE=? "
			+ "AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL update statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForModify(PreparedStatement pStmt, Object obj)
		throws SQLException {

		setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_UPDATE);
	}

	/**
	* Constructs a string of SQL delete statement
	* @return        a string of SQL update statement
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlDelete() {
		String sql = "DELETE FROM T_USER WHERE USER_CODE=? AND COMPANY_CODE=? ";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL delete statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForDelete(PreparedStatement pStmt, Object obj)
		throws SQLException {

		UserVo vo = (UserVo) obj;
		int i = 1;

		pStmt.setString(i++, vo.getUserCode());
		pStmt.setString(i++, vo.getCompanyCode());
	}

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlFind() {

		String sql = "SELECT USER_CODE, COMPANY_CODE, USER_NAME, EMAIL, PASSWORD, "
			+ "ROLE_GROUP_ID, GROUP_LEVEL, ACTIVATION, CREATE_USER, CREATE_DATE, "
			+ "UPDATE_USER, UPDATE_DATE, USER_GROUP_CODE, DEPT, CONTENT_TYPE, "
			+ "SIGNATURE, SALESMAN_FLAG, QA_HANDLER, QC_HANDLER, APPROVER, "
			+ "GMM, DIVISION , COMPANY_TYPE, NT_TEAM, CUST_CODE "
			+ "FROM T_USER "
			+ "WHERE USER_CODE=? AND COMPANY_CODE=? "
			+ "ORDER BY USER_CODE, COMPANY_CODE";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL select statement for finding by primary key.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming PK
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForFind(PreparedStatement pStmt, Object obj)
		throws SQLException {

		UserPk pk = (UserPk) obj;
		int i = 1;

		pStmt.setString(i++, pk.getUserCode());
		pStmt.setString(i++, pk.getCompanyCode());
	}

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlList() {

		return null;
	}

	/**
	* Sets the values for a SQL select statement for finding child tables.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming parent PK
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForList(PreparedStatement pStmt, Object obj)
		throws SQLException {

	}

	/**
	* Sets the values of a result set to a vo for a search screen
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected SimpleBaseVo getSearchResultSetValues(ResultSet rs) throws SQLException {


		return getResultSetValues(rs);
	}

	/**
	* Sets the values of a result set to a vo
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Victor Zheng 
	* @since      FrameWork 1.0
	*/
	protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {

		UserVo vo = new UserVo();

		vo.setUserCode(rs.getString("USER_CODE"));
		vo.setCompanyCode(rs.getString("COMPANY_CODE"));
		vo.setUserName(rs.getString("USER_NAME"));
		vo.setEmail(rs.getString("EMAIL"));
		vo.setPassword(rs.getString("PASSWORD"));
		vo.setRoleGroupId(rs.getString("ROLE_GROUP_ID"));
		vo.setGroupLevel(CustomBigInteger.customize(rs.getBigDecimal("GROUP_LEVEL")));
		vo.setActivation(rs.getString("ACTIVATION"));
		vo.setCreateUser(rs.getString("CREATE_USER"));
		vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
		vo.setUpdateUser(rs.getString("UPDATE_USER"));
		vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
		vo.setUserGroupCode(rs.getString("USER_GROUP_CODE"));
		vo.setDept(rs.getString("DEPT"));
		vo.setContentType(rs.getString("CONTENT_TYPE"));
		vo.setSignature(rs.getBytes("SIGNATURE"));
		vo.setSalesmanFlag(rs.getString("SALESMAN_FLAG"));
		vo.setQaHandlerFlag(rs.getString("QA_HANDLER"));
		vo.setQcHandlerFlag(rs.getString("QC_HANDLER"));
		vo.setApproverFlag(rs.getString("APPROVER"));
		vo.setGmmFlag(rs.getString("GMM"));  
		vo.setUserDivision(rs.getString("DIVISION"));
        vo.setHdrCompanyType(rs.getString("COMPANY_TYPE"));
        vo.setHdrNtTeam(rs.getString("NT_TEAM"));
        vo.setHdrCustCode(rs.getString("CUST_CODE"));
        
		
		return vo;
	}

}
